﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;


namespace StoragePeriod.COMP
{
	/// <summary>
	/// XmlToExcel
	/// </summary>
	public class ExcelWriter {
		string ssns = "urn:schemas-microsoft-com:office:spreadsheet";
		string xmlns = "urn:schemas-microsoft-com:office:spreadsheet";
		XmlDocument _doc = new XmlDocument();
		/// <summary>
		/// _doc.xml
		/// </summary>
		public XmlDocument XslDoc {
			get { return _doc; }
			set { _doc = value; }
		}
		XmlNode _currentSheet = null;
		XmlNode _currentRow = null;

		public ExcelWriter() {
			//excel的xml模版，你需要了解xml的Attributes怎么用
			StringBuilder sbody = new StringBuilder();
			sbody.Append("<?xml version=\"1.0\"?>\n");
			sbody.Append("<?mso-application progid=\"Excel.Sheet\"?>\n");
			sbody.Append("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\n");
			sbody.Append("xmlns:o=\"urn:schemas-microsoft-com:office:office\"\n");
			sbody.Append("xmlns:x=\"urn:schemas-microsoft-com:office:excel\"\n");
			sbody.Append("xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"\n");
			sbody.Append("xmlns:html=\"http://www.w3.org/TR/REC-html40\">\n");
			sbody.Append("<Styles>\n");
			sbody.Append("<Style ss:ID=\"Default\" ss:Name=\"Normal\">\n");
			sbody.Append("<Alignment ss:Vertical=\"Center\"/>\n");
			sbody.Append("<Borders/>\n");
			sbody.Append("<Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"10\"/>\n");
			sbody.Append("<Interior/>\n");
			sbody.Append("<NumberFormat/>\n");
			sbody.Append("<Protection/>\n");
			sbody.Append("</Style>\n");
			sbody.Append("</Styles>\n");
			sbody.Append("</Workbook>\n");
			_doc.LoadXml(sbody.ToString());
		}

		/// <summary>
		/// 增加一个工作表
		/// </summary>
		/// <param name="sheetName">工作表名称</param>
		public void CreateSheet(string sheetName) {
			System.Xml.XmlNode node = _doc.CreateNode(XmlNodeType.Element, "Worksheet", ssns);
			System.Xml.XmlAttribute xa = _doc.CreateAttribute("ss", "Name", xmlns);
			xa.Value = sheetName;
			node.Attributes.Append(xa);
			_doc.ChildNodes[2].AppendChild(node);
			node.AppendChild(_doc.CreateNode(XmlNodeType.Element, "Table", xmlns));
			_currentSheet = node;
		}

		/// <summary>
		/// 增加一行
		/// </summary>
		public void CreateRow() {
			System.Xml.XmlNode node = _doc.CreateNode(XmlNodeType.Element, "Row", xmlns);
			_currentSheet.ChildNodes[0].AppendChild(node);
			_currentRow = node;
		}

		/// <summary>
		/// 增加一列
		/// </summary>
		/// <param name="index">索引</param>
		/// <param name="width">宽度</param>
		public void CreateColumn(int index, float width) {
			System.Xml.XmlNode node = _doc.CreateNode(XmlNodeType.Element, "Column", xmlns);
			System.Xml.XmlAttribute xa = _doc.CreateAttribute("ss", "Index", xmlns);
			xa.Value = index.ToString();
			node.Attributes.Append(xa);
			xa = _doc.CreateAttribute("ss", "Width", xmlns);
			xa.Value = width.ToString();
			node.Attributes.Append(xa);
			_currentSheet.ChildNodes[0].AppendChild(node);
		}

		/// <summary>
		/// 增加一个单元格
		/// </summary>
		/// <param name="value">值</param>
		/// <param name="Type">类型</param>
		/// <param name="Expression">公式</param>
		/// <param name="rowSpan">跨行</param>
		/// <param name="colSpan">跨列</param>
		public void CreateCell(object value, string Type, string Expression, int rowSpan, int colSpan) {
			System.Xml.XmlAttribute xa = null;
			System.Xml.XmlNode nodeCell = _doc.CreateNode(XmlNodeType.Element, "Cell", xmlns);
			_currentRow.AppendChild(nodeCell);
			if (!string.IsNullOrEmpty(Expression)) {
				xa = _doc.CreateAttribute("ss", "Formula", xmlns);
				xa.Value = "=" + Expression;
				nodeCell.Attributes.Append(xa);
			}
			if (--colSpan > 0) {
				xa = _doc.CreateAttribute("ss", "MergeAcross", xmlns);
				xa.Value = colSpan.ToString();
				nodeCell.Attributes.Append(xa);
			}
			if (--rowSpan > 0) {
				xa = _doc.CreateAttribute("ss", "MergeDown", xmlns);
				xa.Value = rowSpan.ToString();
				nodeCell.Attributes.Append(xa);
			}
			System.Xml.XmlNode nodeData = _doc.CreateNode(XmlNodeType.Element, "Data", xmlns);
			xa = _doc.CreateAttribute("ss", "Type", xmlns);
			xa.Value = Type;
			nodeData.Attributes.Append(xa);
			nodeData.InnerText = value.ToString();
			nodeCell.AppendChild(nodeData);
		}

		/// <summary>
		/// 增加一个数字单元格
		/// </summary>
		/// <param name="value"></param>
		public void CreateCellNumber(double value) {
			CreateCell(value, "Number", null, 1, 1);
		}

		/// <summary>
		/// 增加一个字符串单元格
		/// </summary>
		/// <param name="value"></param>
		public void CreateCellString(string value) {
			CreateCell(value, "String", null, 1, 1);
		}

		/// <summary>
		/// 保存
		/// </summary>
		/// <param name="strFile"></param>
		public void Save(string strFile) {
			_doc.Save(strFile);
		}
	}
}
